package vivek.project.pocketmoney.expenses.model
{
	import flash.data.*;
	import flash.errors.*;
	import flash.events.Event;
	import flash.net.LocalConnection;
	import flash.system.System;

	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.core.UIComponent;
	import mx.utils.ObjectUtil;

	import vivek.project.pocketmoney.admin.model.AdminConstants;
	import vivek.project.pocketmoney.admin.model.AdminDataCache;
	import vivek.project.pocketmoney.admin.model.AdminDataModel;
	import vivek.project.pocketmoney.admin.scripts.AdminSQLScript;
	import vivek.project.pocketmoney.common.CommonData;
	import vivek.project.pocketmoney.common.PocketmoneyDataInterface;
	import vivek.project.pocketmoney.common.events.CommonEvents;
	import vivek.project.pocketmoney.common.interfaces.*;
	import vivek.project.pocketmoney.common.logger.PMLogger;
	import vivek.project.pocketmoney.common.utils.ArrayUtils;
	import vivek.project.pocketmoney.common.utils.CommonConstants;
	import vivek.project.pocketmoney.expenses.events.ExpenseEvent;
	import vivek.project.pocketmoney.expenses.scripts.ExpenseSQLScript;

	/**
	 *
	 * @author M8070033
	 */
	public class ExpenseDataModel extends UIComponent implements PocketmoneyDataInterface
	{
		private static var MODULE:String="EXPENSES";
		//private  var _sqlConnection : SQLConnection = CommonData.getConnection();
		private static var instance:ExpenseDataModel;

		public static function getInstance():ExpenseDataModel
		{
			if (!instance)
			{
				/*instance = ExpenseDataModel(
				 Singleton.getInstance("vivek.project.pocketmoney.expenses.model::ExpenseDataModel"));*/
				instance=new ExpenseDataModel();
			}
			return instance;
		}

		public function ExpenseDataModel():void
		{
			loadData();
			adminDataModel.addEventListener("USERCHANGED", loadDataOnEvent);
			addEventListener("CoreDataChanged", loadDataOnEvent);
			addEventListener("ExpenseTransactionDeleted", refreshExpenseDataCache);
			addEventListener("ExpenseTransactionAdded", refreshExpenseDataCache);
			//InvestmentDataModel.getInstance().addEventListener("InvestmentChanged", refreshExpenseDataCache);
			addEventListener("ExpenseTransactionUpdated", refreshExpenseDataCache);
		}
		private var adminDataModel:AdminDataModel=AdminDataModel.getInstance();

		//private var expenseSummaryModel:ExpenseSummaryModel=ExpenseSummaryModel.getInstance();
		public function addCategory(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				//_sqlConnection.open(); 
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.ADD_CATEGORY;
				_statement.parameters[":USERID"]=obj.userid;
				_statement.parameters[":cm_categoryname"]=obj.categoryname;
				_statement.parameters[":cm_categorydesc"]=obj.categorydesc;
				_statement.parameters[":cm_expensetype"]=obj.expensetype;
				_statement.execute();
				refreshCategory(_sqlConnection);
				_sqlConnection.close();
				_statement=null;
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return 1;
		}

		public function addDependentExpenseRecord(obj:Object, randomId:Number, conn:SQLConnection):int
		{
			try
			{
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.1 done");
				var depositObj:Object=new Object();
				obj.istransferrecord=0;
				obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
				addObjToData(obj, randomId, conn);
				//refreshExpenseData(conn);
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}

		public function addDependentTransferRecord(obj:Object, randomId:Number, conn:SQLConnection, refresh:Boolean=true):int
		{
			try
			{
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.1 done");
				var depositObj:Object=new Object();
				var withdrawalObj:Object=new Object();
				obj.istransferrecord=1;
				depositObj=ObjectUtil.copy(obj);
				depositObj.type=0;
				depositObj.exRate=1.0; //adminDataModel.getCurrencyDetailsForAccount(depositObj.account).cu_rate;
				addObjToData(depositObj, randomId, conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.2 done");
				withdrawalObj=ObjectUtil.copy(obj);
				withdrawalObj.type=1;
				withdrawalObj.account=withdrawalObj.transferAccount;
				withdrawalObj.userid=adminDataModel.getAccountForId(withdrawalObj.transferAccount).am_userid;
				withdrawalObj.exRate=adminDataModel.getCurrencyDetailsForAccount(withdrawalObj.transferAccount).cu_rate;
				addObjToData(withdrawalObj, randomId, conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.3 done");
				//if (refresh)
				//refreshExpenseData(conn);
				trace("Expense : addDependentTransferRecord : " + new Date().toTimeString() + " : step 2.5.4 done");
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}

		public function addExpenses(array:Array):int
		{
			try
			{
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_sqlConnection.begin();
				for each (var obj:Object in array)
				{
					var randomId:Number=new Number();
					randomId=Math.round(Math.random() * 1000000000);
					obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
					try
					{
						addObjToData(obj, randomId, _sqlConnection);
					}
					catch (error:Error)
					{
					}
				}
				//refreshExpenseData(_sqlConnection);
				//refreshAccountSummary(_sqlConnection);
				_sqlConnection.commit();
				_sqlConnection.close();
				PMLogger.log("EXPENSE", "EXPENSE ADDED", 0, obj.account, obj.amount, 0, obj.category, obj.date);
				dispatchEvent(new Event("ExpenseTransactionAdded"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
			}
			return 1;
		}

		public function addExpense(obj:Object, _sqlConnection:SQLConnection=null):int
		{
			var createConnection:Boolean=false;
			try
			{
				if (_sqlConnection == null)
				{
					_sqlConnection=CommonData.getConnection();
					createConnection=true;
					_sqlConnection.begin();
				}
				var randomId:Number=new Number();
				randomId=Math.round(Math.random() * 1000000000);
				obj.exRate=adminDataModel.getCurrencyDetailsForAccount(obj.account).cu_rate;
				addObjToData(obj, randomId, _sqlConnection);
				//refreshExpenseData(_sqlConnection);
				//refreshAccountSummary(_sqlConnection);
				if (createConnection)
				{
					_sqlConnection.commit();
					_sqlConnection.close();
				}
				dispatchEvent(new Event("ExpenseTransactionAdded"));
					//PMLogger.log("EXPENSE", "EXPENSE ADDED", 0, obj.account, obj.amount, 0, obj.category, obj.date);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
			}
			return 1;
		}

		public function addPayee(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.ADD_PAYEE;
				_statement.parameters[":pm_payeeName"]=obj.payeeName;
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				refreshPayee(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			finally
			{
				_statement=null;
			}
			return 1;
		}

		public function addRecurssiveExpense(obj:Object, _sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":rm_recursiondate"]=obj.onDate;
				_statement.parameters[":rm_amount"]=obj.amount;
				_statement.parameters[":rm_fromaccount"]=obj.account;
				_statement.parameters[":rm_toaccount"]=obj.toAccount;
				_statement.parameters[":rm_fromdate"]=obj.startDate;
				_statement.parameters[":rm_todate"]=obj.endDate;
				_statement.parameters[":rm_type"]=obj.type;
				_statement.parameters[":rm_category"]=obj.category;
				_statement.parameters[":rm_memo"]=obj.memo;
				_statement.parameters[":rm_status"]=obj.status;
				_statement.parameters[":rm_nextexecutiondate"]=RecurssionInterface.getNextExecutionDate(obj.startDate, obj.endDate, obj.onDate);
				_statement.text=ExpenseSQLScript.ADD_RECURSSIVE_EXPENSE;
				_statement.execute();
				//PMLogger.log(MODULE, "RECURSSION ADDED from: " + obj.account + "to :" + obj.toAccount + "cat:" + obj.category + "from date" + obj.startDate + "end Date" + obj.endDate, 0, obj.account, obj.amount, obj.toAccount, obj.category, obj.onDate);
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.details);
			}
		}

		public function addTransferRecord(obj:Object, _conn:SQLConnection=null):void
		{
			var conn:SQLConnection=new SQLConnection();
			if (_conn == null)
			{
				conn=CommonData.getConnection();
				conn.begin();
				_addTransferRecord(obj, conn);

				conn.commit();
				conn.close();
			}
			else
			{
				//conn == _conn;
				_addTransferRecord(obj, _conn);
			}

			//refreshExpenseData(conn);
			dispatchEvent(new Event("ExpenseTransactionAdded"));
		}

		public function deleteCategory(category:Object, _sqlConnection:SQLConnection):int
		{
			var _statement:SQLStatement=new PMSqlStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.EXPENSE_CATEGORY;
				_statement.parameters[":categoryid"]=category.categoryid;
				_statement.execute();
				var data:Array=data=_statement.getResult().data;
				if (data == null)
				{
					_statement.clearParameters();
					_statement.text=ExpenseSQLScript.DELETE_CATEGORY_RECORD;
					_statement.parameters[":cm_categoryid"]=category.categoryid;
					_statement.execute();
					refreshCategory(_sqlConnection);
				}
				else
					return 9;
			}
			catch (sqlError:SQLError)
			{
				trace("Delete Cateogory : " + category.categoryid + ":" + sqlError.message + ":" + sqlError.details);
			}
			return 1;
		}

		public function deleteDependentExpense(expenseId:Number, refresh:Boolean, _sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":expenseId"]=expenseId;
				_statement.text=ExpenseSQLScript.DELETE_EXPENSE_RECORD;
				_statement.execute();
			}
			catch (sqlError:SQLError)
			{
				throw sqlError;
			}
		}

		public function deleteExpenses(expenses:Array):void
		{
			var connection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			var obj:Object=new Object();
			try
			{
				connection.begin();
				_statement.sqlConnection=connection;
				for each (obj in expenses)
				{
					_statement.parameters[":expenseId"]=obj.em_expenseid;
					_statement.text=ExpenseSQLScript.DELETE_EXPENSE_RECORD;
					_statement.execute();
						//PMLogger.logMultiple("EXPENSE", "EXPENSE DELETED", obj.em_expenseid, obj.am_accountid, obj.em_amount, 0, obj.em_category, obj.em_transactiondate);
				}
				connection.commit();
				connection.close();
			}
			catch (sqlError:SQLError)
			{
				connection.rollback();
				connection.close();
					//Alert.show(sqlError.details);
			}

			switch (obj.em_category)
			{
				case CommonConstants.LOAN_CATEGORY_ID:
					dispatchEvent(new ExpenseEvent(CommonEvents.LOAN_EXPENSE_TRANSACTION_DELETED, obj, 1));
					break;
				case CommonConstants.PROPERTY_CATEGORY_ID:
					dispatchEvent(new ExpenseEvent(CommonEvents.PROPERTY_EXPENSE_TRANSACTION_DELETED, obj, 1));
					break;

			}
			dispatchEvent(new ExpenseEvent("ExpenseTransactionDeleted", obj, 1));
			//PMLogger.flush();
		}

		public function deletePayee(obj:Object, _sqlConnection:SQLConnection):Boolean
		{
			var deleted:Boolean=false;
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var expenseData:Array=new Array();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_EXPENSE_PAYEE;
				_statement.parameters[":payeeid"]=obj.pm_id;
				_statement.execute();
				expenseData=_statement.getResult().data;
				if (expenseData == null)
				{
					_statement=new SQLStatement();
					_statement.sqlConnection=_sqlConnection;
					_statement.text=ExpenseSQLScript.DELETE_PAYEE;
					_statement.parameters[":pm_payeeid"]=obj.pm_id;
					_statement.execute();
					deleted=true;
				}
				refreshPayee(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return deleted;
		}

		public function deleteRecurringExpense(obj:Object):int
		{
			var _statement:SQLStatement=new SQLStatement();
			var connection:SQLConnection=CommonData.getConnection();
			try
			{
				_statement.sqlConnection=connection;
				_statement.text=ExpenseSQLScript.DELETE_RECURRING_EXPENSE;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.execute();
				refreshRecurringExpense(connection);
				connection.close();
				//PMLogger.log("EXPENSE", "DELETE RECURRING EXPENSE", 0, obj.account, obj.amount, 0, obj.category, obj.date);
			}
			catch (sqlError:SQLError)
			{
				trace("Expense :deleteRecurringExpense : " + obj.rm_id + " " + sqlError.details);
				return 1;
			}
			return 0;
		}

		public function getCategoryDetailsByName(name:String):Object
		{
			var categoryObj:Object=new Object();
			for each (categoryObj in ExpenseDataCache.allCategoryData)
			{
				if (categoryObj.cm_categoryname == name)
					return categoryObj;
			}
			return categoryObj;
		}

		public function getCategoryDetailsForCatId(categoryId:int):String
		{
			var catObj:Object=new Object();
			for each (catObj in ExpenseDataCache.allCategoryData)
			{
				if (catObj.cm_categoryid == categoryId)
					return catObj.cm_categoryname;
			}
			return "";
		}

		public function getCategoryIdByName(name:String):int
		{
			var categoryObj:Object=new Object();
			for each (categoryObj in ExpenseDataCache.allCategoryData)
			{
				if (categoryObj.cm_categoryname == name)
					return categoryObj.cm_categoryid;
			}
			return 0;
		}

		public function getCategoryNameForCatId(categoryId:int):String
		{
			var catObj:Object=new Object();
			for each (catObj in ExpenseDataCache.allCategoryData)
			{
				if (catObj.cm_categoryid == categoryId)
					return catObj.cm_categoryname;
			}
			return "";
		}

		public function getExpenseForPayee(id:int, _sqlConnection:SQLConnection):Array
		{
			var expenseDetails:Array=new Array();
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_EXPENSE_PAYEE;
				_statement.parameters[":payeeid"]=id;
				_statement.execute();
				expenseDetails=_statement.getResult().data
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return expenseDetails;
		}

		public function getExpenseSummaryData(year:int):Array
		{
			var obj:Object=new Object();
			var arr:Array=new Array();
			for each (obj in ExpenseDataCache.expenseSummaryData)
			{
				if (obj.em_year == year)
					arr.push(obj)
			}
			return arr;
		}

		public function getPayeeDetailsByName(payeeName:String):Object
		{
			var payeeObj:Object=new Object();
			for each (payeeObj in ExpenseDataCache.payeeData)
			{
				if (payeeObj.pm_payeeName == payeeName)
					return payeeObj;
			}
			return payeeObj;
		}

		public function getPayeeIdByName(payeeName:String):int
		{
			var payeeObj:Object=new Object();
			for each (payeeObj in ExpenseDataCache.payeeData)
			{
				if (payeeObj.pm_payeeName == payeeName)
					return payeeObj.pm_id;
			}
			return 0;
		}

		public function getTransferAccountName(expenseid:int, accountid:int, _sqlConnection:SQLConnection):String
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.SELECT_TRANSFER_ACCOUNTNAME;
				_statement.parameters[":accountid"]=accountid;
				_statement.parameters[":expenseid"]=expenseid;
				_statement.execute();
				var accountNameList:ArrayCollection=new ArrayCollection(_statement.getResult().data);
				if (accountNameList.length > 0)
				{
					var accountName:String=accountNameList.getItemAt(0).am_accountname;
					return accountName;
				}
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("can not update : " + sqlError.details);
			}
			return "";
		}

		public function getValidExpenseRecurssions():Array
		{
			var reObj:Object=new Object();
			var reArray:Array=new Array();
			for each (reObj in ExpenseDataCache.recurringExpenseData)
			{
				if (reObj.rm_status == 1)
					reArray.push(reObj);
			}
			return reArray;
		}

		public function loadData():void
		{
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			//_sqlConnection.open();
			var _statement:SQLStatement=new SQLStatement();
			refreshExpenseData(_sqlConnection);
			refreshPayee(_sqlConnection);
			refreshCategory(_sqlConnection);
			refreshAccountSummary(_sqlConnection);
			refreshRecurringExpense(_sqlConnection);
			refreshYears(_sqlConnection);
			_sqlConnection.close();
			refreshDataGrid(null);
			flash.system.System.gc();
			flash.system.System.gc();
		}

		public function refreshExpenseDataCache(event:Event):void
		{
			trace("Expense : refreshExpenseCache : " + new Date().toTimeString() + " : Refresh Started");
			var connection:SQLConnection=CommonData.getConnection();
			refreshExpenseData(connection);
			refreshAccountSummary(connection);
			connection.close();
			refreshDataGrid(event);
			trace("Expense : refreshExpenseCache : " + new Date().toTimeString() + " : Refresh Ended");
			dispatchEvent(new Event("ExpenseDataCacheChanged"));
		}

		public function refreshPayee(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text="select * from payeemanager where pm_userid  in (select userid from selecteduser)";
			//_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
			_statement.execute();
			ExpenseDataCache.payeeData=new ArrayCollection(_statement.getResult().data);
		}

		public function updateCategory(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_CATEGORY;
				_statement.parameters[":cm_categoryname"]=obj.categoryName;
				_statement.parameters[":cm_expensetype"]=obj.expensetype;
				_statement.parameters[":cm_categorydesc"]=obj.description;
				_statement.parameters[":cm_categoryid"]=obj.categoryId;
				_statement.execute();
				loadData();
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function updateDependentExpense(obj:Object, expenseId:Number, conn:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=conn;
			_statement.text=ExpenseSQLScript.UPDATE_EXPENSE_RECORD;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.execute();
			//refreshExpenseData(conn);
			dispatchEvent(new Event("ExpenseTransactionUpdated"));
		}

		public function updateDependentTransferRecord(obj:Object, randomId:Number, conn:SQLConnection):int
		{
			try
			{
				deleteDependentExpense(randomId, true, conn);
				addDependentTransferRecord(obj, randomId, conn, true);
				/*
				   trace(new Date().toLocaleTimeString() + " start edit- step 2.5.1 done ");
				   var depositObj : Object = new Object();
				   var withdrawalObj : Object = new Object();
				   depositObj = ObjectUtil.copy(obj);
				   depositObj.type = 0;
				   depositObj.exRate = adminDataModel.getCurrencyDetailsForAccount(depositObj.account).cu_rate;
				   updateTransferExpense(depositObj , randomId);
				   trace(new Date().toLocaleTimeString() + " start edit- step 2.5.2 done ");
				   withdrawalObj = ObjectUtil.copy(obj);
				   withdrawalObj.type = 1;
				   withdrawalObj.account = withdrawalObj.transferAccount;
				   withdrawalObj.exRate = adminDataModel.getCurrencyDetailsForAccount(withdrawalObj.transferAccount).cu_rate;
				   withdrawalObj.amount = obj.amount ;
				   updateTransferExpense(withdrawalObj , randomId);
				   trace(new Date().toLocaleTimeString() + " start edit- step 2.5.3 done ");
				   refreshExpenseData();
				   trace(new Date().toLocaleTimeString() + " start edit- step 2.5.4 done ");
				 */
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}

		/*
		   public function updateTransferExpense(obj : Object , expenseId : Number) : void
		   {
		   var _statement : SQLStatement = new SQLStatement();
		   _statement.sqlConnection = _sqlConnection;
		   _statement.text = ExpenseSQLScript.UPDATE_TRANSFER_EXPENSE_RECORD;
		   _statement.parameters[":category"] = obj.category;
		   _statement.parameters[":amount"] = obj.amount;
		   _statement.parameters[":payee"] = obj.payee;
		   _statement.parameters[":memo"] = obj.memo;
		   _statement.parameters[":account"] = obj.account;
		   _statement.parameters[":date"] = obj.date;
		   _statement.parameters[":exrate"] = obj.exRate;
		   _statement.parameters[":expenseid"] = expenseId;
		   _statement.parameters[":type"] = obj.type;
		   _statement.execute();
		   loadData();
		   }

		   /*

		   public function updateTransferExpense(obj : Object , expenseId : Number) : void
		   {

		   var _statement : SQLStatement = new SQLStatement();
		   _statement.sqlConnection = _sqlConnection;
		   _statement.text = ExpenseSQLScript.UPDATE_TRANSFER_EXPENSE_RECORD;
		   _statement.parameters[":category"] = obj.category;
		   _statement.parameters[":amount"] = obj.amount;
		   _statement.parameters[":payee"] = obj.payee;
		   _statement.parameters[":memo"] = obj.memo;
		   _statement.parameters[":account"] = obj.account;
		   _statement.parameters[":date"] = obj.date;
		   _statement.parameters[":exrate"] = obj.exRate;
		   _statement.parameters[":expenseid"] = expenseId;
		   _statement.parameters[":type"] = obj.type;
		   _statement.execute();
		   loadData();
		   }
		 */
		public function updateExpense(obj:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=sqlConnection;
			_statement.text=ExpenseSQLScript.UPDATE_EXPENSE_RECORD;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.execute();
			//refreshExpenseData(sqlConnection);
			sqlConnection.close();
			//PMLogger.log("EXPENSE", "EXPENSE UPDATED", expenseId, obj.account, obj.amount, 0, obj.category, obj.date);
			dispatchEvent(new Event("ExpenseTransactionUpdated"));
		}

		public function updateExpenseField(fieldName:String, value:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="UPDATE EXPENSEMANAGER SET " + fieldName + " = :VALUE WHERE em_expenseid=:expenseid";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":expenseid"]=expenseId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				//PMLogger.log("EXPENSE", "EXPENSE UPDATED :" + expenseId + " , field " + fieldName + "," + value);
				dispatchEvent(new Event("ExpenseTransactionUpdated"));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}

		public function updateRecurringExpenseField(fieldName:String, value:Object, expenseId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_EXPENSE_RECORD_FIELD:String="UPDATE recurringexpensemanager  SET " + fieldName + " = :VALUE WHERE rm_id=:expenseid";
				_statement.text=UPDATE_EXPENSE_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":expenseid"]=expenseId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				refreshRecurringExpense(sqlConnection);
				sqlConnection.close();
				_statement=null;
					//PMLogger.log("EXPENSE", "EXPENSE UPDATED :" + expenseId + " , field " + fieldName + "," + value);
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}

		public function updatePayee(obj:Object, _sqlConnection:SQLConnection):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_PAYEE;
				_statement.parameters[":pm_payeeName"]=obj.payeeName;
				_statement.parameters[":pm_id"]=obj.payeeId;
				_statement.execute();
				loadData();
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function updateRecurrsiveExpenseNextExecutionData(obj:Object, _sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.text=ExpenseSQLScript.UPDATE_RECURRING_EXPENSE_NEXT_EXECUTION_DATE;
				_statement.parameters[":rm_nextexecutiondate"]=obj.nextExecutionDate;
				_statement.parameters[":rm_last_executed_date"]=obj.lastExecutedDate;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.execute();
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("can not update : " + sqlError.details);
				_sqlConnection.rollback();
			}
		}

		public function updateRecurssiveExpenseInfo(obj:Object, _sqlConnection:SQLConnection):int
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=_sqlConnection;
				_statement.parameters[":rm_recursiondate"]=obj.onDate;
				_statement.parameters[":rm_amount"]=obj.amount;
				_statement.parameters[":rm_fromaccount"]=obj.account;
				_statement.parameters[":rm_toaccount"]=obj.toAccount;
				_statement.parameters[":rm_fromdate"]=obj.startDate;
				_statement.parameters[":rm_todate"]=obj.endDate;
				_statement.parameters[":rm_type"]=obj.type;
				_statement.parameters[":rm_category"]=obj.category;
				_statement.parameters[":rm_memo"]=obj.memo;
				_statement.parameters[":rm_status"]=obj.status;
				_statement.parameters[":rm_id"]=obj.rm_id;
				_statement.parameters[":rm_nextexecutiondate"]=RecurssionInterface.getNextExecutionDate(obj.startDate, obj.endDate, obj.onDate);
				_statement.text=ExpenseSQLScript.UPDATE_RECURSSIVE_EXPENSE;
				_statement.execute();
				refreshRecurringExpense(_sqlConnection);
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.details);
				return 1;
			}
			return 0;
		}

		public function updateTransferRecord(obj:Object):int
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			try
			{
				// Delete the record 
				sqlConnection.begin();
				deleteDependentExpense(obj.expenseid, true, sqlConnection);
				// Add new record				
				_addTransferRecord(obj, sqlConnection);
				sqlConnection.commit();
				//refreshExpenseData(sqlConnection);
				dispatchEvent(new Event("ExpenseTransactionUpdated"));
			}
			catch (sqlError:SQLError)
			{
				sqlConnection.rollback();
				sqlConnection.close();
				//Alert.show(sqlError.details);
				return 1;
			}
			sqlConnection.close();
			return 0;
		}

		private function _addTransferRecord(obj:Object, conn:SQLConnection):int
		{
			try
			{
				//var conn : SQLConnection = CommonData.getConnection();
				var randomId:Number=new Number();
				var depositObj:Object=new Object();
				var withdrawalObj:Object=new Object();
				depositObj=ObjectUtil.copy(obj);
				depositObj.type=0;
				depositObj.exRate=adminDataModel.getCurrencyDetailsForAccount(depositObj.account).cu_rate;
				randomId=Math.round(Math.random() * 1000000000);
				addObjToData(depositObj, randomId, conn);
				withdrawalObj=ObjectUtil.copy(obj);
				withdrawalObj.type=1;
				withdrawalObj.account=withdrawalObj.transferAccount;
				withdrawalObj.userid=adminDataModel.getAccountForId(withdrawalObj.transferAccount).am_userid;
				withdrawalObj.exRate=adminDataModel.getCurrencyDetailsForAccount(withdrawalObj.transferAccount).cu_rate;
				addObjToData(withdrawalObj, randomId, conn);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
				return 1;
			}
			return 0;
		}

		private function addObjToData(obj:Object, expenseId:Number, conn:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=conn;
			_statement.text=ExpenseSQLScript.EXPENSE_ADD_RECORD;
			_statement.parameters[":account"]=obj.account;
			_statement.parameters[":amount"]=obj.amount;
			_statement.parameters[":category"]=obj.category;
			_statement.parameters[":payee"]=obj.payee;
			_statement.parameters[":memo"]=obj.memo;
			_statement.parameters[":type"]=obj.type;
			_statement.parameters[":date"]=obj.date;
			_statement.parameters[":expenseid"]=expenseId;
			_statement.parameters[":exrate"]=obj.exRate;
			_statement.parameters[":instru"]=obj.instru;
			_statement.parameters[":USERID"]=obj.userid;
			if (obj.istransferrecord == null)
				_statement.parameters[":istransferrecord"]=0;
			else
				_statement.parameters[":istransferrecord"]=obj.istransferrecord;
			_statement.execute();
			_statement=null;
		}

		private function flattenCategoryArray(categoryArray:Array):Array
		{
			var flatArray:Array=new Array();
			var catObj:Object=new Object()
			var monthObj:Object=new Object();
			monthObj.category="Total";
			monthObj.January=0;
			monthObj.Feburary=0;
			monthObj.March=0;
			monthObj.April=0;
			monthObj.May=0;
			monthObj.June=0;
			monthObj.July=0;
			monthObj.August=0;
			monthObj.September=0;
			monthObj.October=0;
			monthObj.November=0;
			monthObj.December=0;
			monthObj.Total=0;
			for each (catObj in categoryArray)
			{
				var tempObj:Object=new Object;
				tempObj.em_year=catObj.em_year;
				tempObj.cm_expensetype=catObj.cm_expensetype
				tempObj.category=catObj.cm_categoryname
				var i:int=0;
				for (i=1; i <= 13; i++)
				{
					switch (i)
					{
						case 1:
							tempObj.January=catObj.data.January;
							//    monthObj.January=catObj.data.January+monthObj.January;
							break;
						case 2:
							tempObj.Feburary=catObj.data.Feburary;
							//   monthObj.Feburary=catObj.data.Feburary+monthObj.Feburary;
							break;
						case 3:
							tempObj.March=catObj.data.March;
							//   monthObj.March=catObj.data.March+monthObj.March;
							break;
						case 4:
							tempObj.April=catObj.data.April;
							//    monthObj.April=catObj.data.April+monthObj.April;
							break;
						case 5:
							tempObj.May=catObj.data.May;
							//   monthObj.May=catObj.data.May+monthObj.May;
							break;
						case 6:
							tempObj.June=catObj.data.June;
							//	monthObj.June=catObj.data.June+monthObj.June;
							break;
						case 7:
							tempObj.July=catObj.data.July;
							//   monthObj.July=catObj.data.July+monthObj.July;;
							break;
						case 8:
							tempObj.August=catObj.data.August;
							//  monthObj.August=catObj.data.August;
							break;
						case 9:
							tempObj.September=catObj.data.September;
							//  monthObj.September=catObj.data.September++monthObj.September;;
							break;
						case 10:
							tempObj.October=catObj.data.October;
							// monthObj.October=catObj.data.October+monthObj.October;
							break;
						case 11:
							tempObj.November=catObj.data.November;
							// monthObj.November=catObj.data.November+monthObj.November;;
							break;
						case 12:
							tempObj.December=catObj.data.December;
							//monthObj.December=catObj.data.December+monthObj.December;
							break;
						case 13:
							tempObj.Total=catObj.data.catTotal;
							// monthObj.Total=catObj.data.catTotal++monthObj.Total;
							break;
					}
				}
				flatArray.push(tempObj);
			}
			//flatArray.push(monthObj);
			//trace (flatArray);
			return flatArray;
		}

		private function getExpensePivotData():Array
		{
			var categoryArray:Array=new Array();
			var obj:Object=new Object();
			var len:Array=new Array();
			for each (obj in ExpenseDataCache.expenseData)
			{
				//if (obj.em_year == yearCombo.selectedItem)
				{
					len=ArrayUtils.findMatchingItem(obj, categoryArray, ["em_year", "cm_categoryname"]);
					var field:String="";
					switch (obj.em_month)
					{
						case "01":
							field="January";
							break;
						case "02":
							field="Feburary";
							break;
						case "03":
							field="March";
							break;
						case "04":
							field="April";
							break;
						case "05":
							field="May";
							break;
						case "06":
							field="June";
							break;
						case "07":
							field="July";
							break;
						case "08":
							field="August";
							break;
						case "09":
							field="September";
							break;
						case "10":
							field="October";
							break;
						case "11":
							field="November";
							break;
						case "12":
							field="December";
							break;
						case "13":
							field="Total";
							break;
					}
					if (len.length == 0) //no item found
					{
						var obj1:Object=new Object();
						var i:int=0;
						obj1["em_year"]=obj.em_year;
						obj1["cm_expensetype"]=CommonConstants.getExpenseTypeString(obj.cm_expensetype);
						obj1["cm_categoryname"]=obj.cm_categoryname;
						obj1["data"]=new Object();
						obj1.data.January=0;
						obj1.data.Feburary=0
						obj1.data.March=0
						obj1.data.April=0
						obj1.data.May=0
						obj1.data.June=0
						obj1.data.July=0
						obj1.data.August=0
						obj1.data.September=0
						obj1.data.October=0
						obj1.data.November=0
						obj1.data.December=0
						obj1.data.catTotal=0
						var amount:int=0;
						if (obj.em_expensetype == AdminConstants.WITHDRAWAL)
						{
							amount=obj.em_amount * -1;
						}
						else
						{
							amount=obj.em_amount;
						}
						obj1["data"][field]=amount;
						obj1["data"]["catTotal"]=amount;
						categoryArray.push(obj1);
					}
					else
					{
						amount=0;
						if (obj.em_expensetype == AdminConstants.WITHDRAWAL)
							amount=obj.em_amount * -1;
						else
							amount=obj.em_amount;
						len[0]["data"][field]=len[0]["data"][field] + amount;
						len[0]["data"]["catTotal"]=len[0]["data"]["catTotal"] + amount;
					}
				}
			}
			categoryArray.sortOn(["em_year", "cm_expensetype", "cm_categoryname"]);
			return categoryArray;
		}

		private function loadDataOnEvent(event:Event):void
		{
			loadData();
			flash.system.System.gc();
			flash.system.System.gc();
			try
			{
				new LocalConnection().connect('foo');
				new LocalConnection().connect('foo');
			}
			catch (e:*)
			{
			}
		}

		private function matchAccount(account:int, array:ArrayCollection):Object
		{
			var obj1:Object=new Object();
			for each (obj1 in array)
			{
				if (obj1.em_accountid == account)
				{
					return obj1
				}
			}
			return null;
		}

		/*private function refreshExpenseSummaryData() : void
		   {
		   trace ("Expense : refreshExpenseSummaryData : "+new Date().toTimeString() + " : Refresh Started");
		   var _statement : SQLStatement = new SQLStatement();
		   _statement.sqlConnection = CommonData.getConnection();
		   _statement.text = ExpenseSQLScript.EXPENSE_SUMMARY_DATA;
		   _statement.execute();
		   trace ("Expense : refreshExpenseSummaryData : "+new Date().toTimeString() + " : Executed");
		   ExpenseDataCache.expenseSummaryData = new ArrayCollection(_statement.getResult().data);
		   trace ("Expense : refreshExpenseSummaryData : "+new Date().toTimeString() + " : Refresh completed ");
		   //ExpenseDataCache.expenseSummaryData.dispatchEvent(new Event("expenseSummaryChanged"));
		 }*/
		private function refreshAccountSummary(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			trace("Expense : refreshExpenseSummary : " + new Date().toTimeString() + " : Refresh Started");
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.ACCOUNT_SUMMARY;
			_statement.execute();
			var obj:Object=new Object();
			var obj1:Object=new Object();
			ExpenseDataCache.accountSummary.source=new Array(); //removeAll();
			for each (obj in _statement.getResult().data)
			{
				obj1=matchAccount(obj.em_accountid, ExpenseDataCache.accountSummary);
				if (obj1 == null)
				{
					if (obj.em_expensetype == 0)
					{
						obj.amount=obj.amount * -1;
					}
					ExpenseDataCache.accountSummary.addItem(obj);
				}
				else
				{
					if (obj.em_expensetype == 1)
						obj1.amount=obj1.amount + obj.amount;
					else
						obj1.amount=obj1.amount - obj.amount;
				}
			}
			_statement=null;
			trace("Expense : refreshExpenseSummary : " + new Date().toTimeString() + " : Refresh Started");
		}

		private function refreshCategory(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=AdminSQLScript.SELECT_CATEGORY_ALL;
			_statement.execute();
			ExpenseDataCache.allCategoryData=new ArrayCollection(_statement.getResult().data);
			_statement=new SQLStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text="select * from categorymanager where cm_categorytype!=2";
			_statement.execute();
			ExpenseDataCache.expenseCategoryData.source=_statement.getResult().data;
			//ExpenseDataCache.categoryData.refresh();
			_statement=null;
		}

		private function refreshDataGrid(e:Event):void
		{
			//Alert.show("dddd");
			var array1:Array=getExpensePivotData();
			ExpenseDataCache.expenseSummaryData.source=new Array();
			ExpenseDataCache.expenseSummaryData=new ArrayCollection(flattenCategoryArray(array1));
		}

		private function refreshExpenseData(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new SQLStatement();
			trace("Expense : refreshExpenseData " + new Date().toTimeString() + " : Before Refresh");
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.SELECT_FILTERED_EXPENSES_ALL;
			_statement.execute();
			//ExpenseDataCache.expenseData.disableAutoUpdate();
			//ExpenseDataCache.expenseData.removeAll();
			//trace(new Date().toLocaleTimeString() + " refresh ex data start 2");
			//ArrayUtils.copyAllElementsFromArray(_statement.getResult().data,ExpenseDataCache.expenseData );
			ExpenseDataCache.expenseData.source=_statement.getResult().data;
			for each (var obj_data:Object in ExpenseDataCache.expenseData)
				if (obj_data.em_expensetype == 1)
				{
					obj_data.cr_amount=obj_data.em_amount;
					obj_data.dr_amount=0;
				}
				else
				{
					obj_data.dr_amount=obj_data.em_amount;
					obj_data.cr_amount=0;
				}

			trace("Expense : refreshExpenseData " + new Date().toTimeString() + " : After Refresh");
			//dispatchEvent(new Event("ExpenseDataCacheChanged"));
			//ExpenseDataCache.expenseData.dispatchEvent(new Event("expenseDataChanged"));
			//refreshYears(_sqlConnection);
			//refreshExpenseSummaryData();
		}

		private function refreshRecurringExpense(_sqlConnection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=ExpenseSQLScript.SELECT_RECURRING_EXPENSES_ALL;
			_statement.execute();
			ExpenseDataCache.recurringExpenseData.source=_statement.getResult().data;
		}

		private function refreshYears(connection:SQLConnection):void
		{
			var _statement:SQLStatement=new PMSqlStatement();
			_statement.sqlConnection=connection;
			_statement.text=ExpenseSQLScript.EXPENSE_YEARS;
			_statement.execute();
			ExpenseDataCache.yearData.source=_statement.getResult().data;
		}
	}
}